# -*- coding: utf-8 -*-

'''
Class to create the html files

Created on 2011/03/31
@author: Edgar Pereira 
Nº 6207

'''

import sqlite3
import os

class MyHTML():
    def __init__(self):
        pass

    
    #-------------------------------------------------------------------------
    # Create index.html file
    #-------------------------------------------------------------------------
    def criar_index(self):
        print('Creating index')
        file = open('index.html', "wb") # opens file for writing
        # the head of html
        file.write('<head>')
        file.write('<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />')
        file.write('<h1 align="center">Página de agregação do indice da estatistica rebides</h1>\n') # Header
        file.write('</head>')
        # the body of html
        file.write('<body>')
        # New table
        file.write('<table border="1" align="center" cellspacing="0" bgcolor=#CCCC99>')
        # New table row
        file.write('<tr>')
        file.write('<td bgcolor="#CCCC19"><div align="center"><strong>Estatistica da Página Rebides</strong></div></td>')
        file.write('</tr>')
        file.write('<tr>')
        file.write('<td>Nº Total de Professores por Ano -<a href="HTML/totprof.html">  clique aqui</td>')
        file.write('</tr>')
        file.write('<tr>')
        file.write('<td>Nº Total de Professores por Insituição do Ensino Superior e por Ano (10+) -<a href="HTML/TotProfEstAnos.html"> clique aqui</td>')
        file.write('</tr>')
        file.write('<tr>')
        file.write('<td>Nº Total de Professores por Grau e Ano (10+) -<a href="HTML/TotProfGrauAnos.html"> clique aqui</td>')
        file.write('</tr>')
        file.write('<tr>')
        file.write('<td>Nº Total de Professores por Insituição, Grau e Ano (20+) -<a href="HTML/TotProfGrauEstAnos.html"> clique aqui</td>')
        file.write('</tr>')
        file.write('<tr>')
        file.write('<td>Lista de Instituições do Ensino Superior por Ano -<a href="HTML/list_est_2000.html"> clique aqui</td>')
        file.write('</tr>')
        file.write('<tr>')
        file.write('<td>Lista de Detentores de Grau Académico por Ano -<a href="HTML/list_prof_grau_ano_2000.html"> clique aqui</td>')
        file.write('</tr>')
        file.write('</table>') # close table
        file.write('</body>') # close body
        file.write('</html>') # close html
        file.close # close the file
        print('Index created')
        pass
    
    
    
    #-------------------------------------------------------------------------
    # Total nº of teachers in the higher education system per year
    #-------------------------------------------------------------------------
    def tot_prof_ano(self):
        print ('Total nº of teachers in the higher education system per year')
        path = 'HTML/totprof.html'
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        tot_prof_ano = open(path,"wb") # opens file for writing
        # Then start to build the html code
        tot_prof_ano.write('<html>\n')
        tot_prof_ano.write('<head align="center">')
        tot_prof_ano.write('<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />')
        tot_prof_ano.write('<h1 align="center">Nº Total de Professores por Ano</h1>\n') # Header
        tot_prof_ano.write('</head>')
        tot_prof_ano.write('<body>\n')
        # some nedded links
        tot_prof_ano.write('<table border =1 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n') # new table
        tot_prof_ano.write('<td><a href="../index.html">-inicio-</a></td>')
        tot_prof_ano.write('</table>\n') # closes the table
        
        # Here starts the table and we can change the appearence
        tot_prof_ano.write('<table border =2 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC99>\n') # Creates a table
        for ano in range(0, 10):
            print('Processing year 200{0}'.format(ano))
            conn = sqlite3.connect('rebides200{0}.db'.format(ano))
            cursor = conn.cursor()
            try:
                cmd = """SELECT COUNT (*) FROM docentes"""
                cursor.execute(cmd)
                r = cursor.fetchone()
            except:
                print ('ERROR')
            pass
        
            tot_prof_ano.write('<tr>')
            tot_prof_ano.write('<td>Nº Total de Professores no ano de 200{0} </td>'.format(ano)) # Creates a new colum in the row
            tot_prof_ano.write('<td> %d </td>' %(r[0])) # Creates a new colum in the row                
            tot_prof_ano.write('</tr>\n')# Closes the row
            cursor.close()
        
        tot_prof_ano.write('</table>\n') # closes the table
        # some nedded links
        tot_prof_ano.write('<table border =1 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n') # new table
        tot_prof_ano.write('<td><a href="../index.html">-inicio-</a></td>')
        tot_prof_ano.write('</table>\n') # closes the table
        
        tot_prof_ano.write('</body>\n')
        tot_prof_ano.write('</html>\n')
        tot_prof_ano.close # close the file
    
    
    #-------------------------------------------------------------------------
    # Total nº of teachers per institution and per year
    #-------------------------------------------------------------------------
    def total_prof_est_ano(self):
        print ('Total nº of teachers per institution and per year')
        path = 'HTML/TotProfEstAnos.html'
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        file = open(path,"wb") # opens file for writing
        # Then start to build the html code
        file.write('<html>\n')
        file.write('<head>')
        file.write('<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />')
        file.write('<h1 align="center">Nº Total de Professores por Estabelecimento e por Ano (10+)</h1>\n') # Header
        file.write('</head>')
        file.write('<body>\n')
        # some nedded links
        file.write('<table border =1 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n') # new table
        file.write('<td><a href="../index.html">-inicio-</a></td>')
        file.write('</table>\n') # closes the table
        
        # Here starts the table and we can change the appearence
        file.write('<table border =2 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC99>\n') # Creates a table
              
        for ano in range(0, 10):
            print('Processing year 200{0}'.format(ano))
            file.write('<tr border =2 bordercolor=000000 cellspacing=0 bgcolor=#CCCC19>\n')
            file.write('<td colspan="2" bgcolor="#CCCC00"><strong>ANO DE 200{0}</strong></td>\n'.format(ano)) # Creates a new colum in the row)
            file.write('</tr>\n')
            file.write('<tr>\n')
            file.write('<td>INSTITUIÇÃO</td><td>Nº PROFS</td>\n') # Creates a new colum in the row)  
            conn = sqlite3.connect('rebides200{0}.db'.format(ano))
            cursor = conn.cursor()
            try:
                cmd = """SELECT     estabelecimento,
                         COUNT(DISTINCT docente) AS Total_docentes
                         FROM       fichas_docencia
                         GROUP BY	estabelecimento
                         ORDER by   Total_docentes DESC
                         LIMIT 10"""
                cursor.execute(cmd)
                r = cursor.fetchall()
                for row in r:
                    file.write('<tr>\n')
                    file.write('<td>%s</td>' %(row[0].encode('utf-8'))) # Creates a new colum in the row)
                    file.write('<td>%d</td>' %(row[1])) # Creates a new colum in the row)
                    file.write('</tr>\n')
            except:
                print ('ERROR')
            pass
            file.write('</tr>\n')
        
        cursor.close()                
        file.write('</table>\n') # closes the table
        # some nedded links
        file.write('<table border =1 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n') # new table
        file.write('<td><a href="../index.html">-inicio-</a></td>')
        file.write('</table>\n') # closes the table
        
        file.write('</body>\n')
        file.write('</html>\n')
        file.close # close the file
    pass
    
    
    #-------------------------------------------------------------------------
    # Total nº of teachers per degree and per year
    #-------------------------------------------------------------------------
    def total_prof_grau_ano(self):
        print ('Total nº of teachers per degree and per year')
        path = 'HTML/TotProfGrauAnos.html'
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        file = open(path,"wb") # opens file for writing
        # Then start to build the html code
        file.write('<html>\n')
        file.write('<head>')
        file.write('<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />')
        file.write('<h1 align="center">Nº Total de Professores por Grau e por Ano (10+)</h1>\n') # Header
        file.write('</head>')
        file.write('<body>\n')
        # some nedded links
        file.write('<table border =1 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n') # new table
        file.write('<td><a href="../index.html">-inicio-</a></td>')
        file.write('</table>\n') # closes the table
        
        # Here starts the table and we can change the appearence
        file.write('<table border =2 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC99>\n') # Creates a table
              
        for ano in range(0, 10):
            print('Processing year 200{0}'.format(ano))
            file.write('<tr border =2 bordercolor=000000 cellspacing=0 bgcolor=#CCCC19>\n')
            file.write('<td colspan="2" bgcolor="#CCCC00"><strong>DADOS DO ANO DE 200{0}</strong></td>\n'.format(ano)) # Creates a new colum in the row)
            file.write('</tr>\n')
            file.write('<tr>\n')
            file.write('<td>GRAU</td><td>Nº PROFS</td>\n') # Creates a new colum in the row)  
            conn = sqlite3.connect('rebides200{0}.db'.format(ano))
            cursor = conn.cursor()
            try:
                cmd = """SELECT     grau,
                         COUNT(DISTINCT docente) AS Total_docentes
                         FROM       fichas_docencia
                         GROUP BY	grau
                         ORDER by   Total_docentes DESC
                         LIMIT 10"""
                cursor.execute(cmd)
                r = cursor.fetchall()
                for row in r:
                    file.write('<tr>\n')
                    file.write('<td>%s</td>' %(row[0].encode('utf-8'))) # Creates a new colum in the row)
                    file.write('<td>%d</td>' %(row[1])) # Creates a new colum in the row)
                    file.write('</tr>\n')
            except:
                print ('ERROR')
            pass
            file.write('</tr>\n')
        
        cursor.close()                
        file.write('</table>\n') # closes the table
        file.write('<table border =1 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n') # new table
        file.write('<td><a href="../index.html">-inicio-</a></td>')
        file.write('</table>\n') # closes the table
        
        file.write('</body>\n')
        file.write('</html>\n')
        file.close # close the file
    pass
    
    
    #-------------------------------------------------------------------------
    # Total nº of teachers per institution per degree and per year
    #-------------------------------------------------------------------------
    def total_prof_grau_est_ano(self):
        print ('Total nº of teachers per institution per degree and per year')
        path = 'HTML/TotProfGrauEstAnos.html'
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        file = open(path,"wb") # opens file for writing
        # Then start to build the html code
        file.write('<html>\n')
        file.write('<head>')
        file.write('<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />')
        file.write('<h1 align="center">Nº Total de Professores por Grau, Estabelecimento e por Ano (20+)</h1>\n') # Header
        file.write('</head>')
        file.write('<body>\n')
        # some nedded links
        file.write('<table border =1 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n') # new table
        file.write('<td><a href="../index.html">-inicio-</a></td>')
        file.write('</table>\n') # closes the table
        
        # Here starts the table and we can change the appearence
        file.write('<table border =2 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC99>\n') # Creates a table
              
        for ano in range(0, 10):
            print('Processing year 200{0}'.format(ano))
            file.write('<tr border =2 bordercolor=000000 cellspacing=0 bgcolor=#CCCC19>\n')
            file.write('<td colspan="3" bgcolor="#CCCC00"><strong>ANO DE 200{0}</strong></td>\n'.format(ano)) # Creates a new colum in the row)
            file.write('</tr>\n')
            file.write('<tr>\n')
            file.write('<td>ESTABELECIMENTO</td><td>GRAU</td><td>Nº PROFS</td>\n') # Creates a new colum in the row)  
            conn = sqlite3.connect('rebides200{0}.db'.format(ano))
            cursor = conn.cursor()
            try:
                cmd = """SELECT estabelecimento, grau,
                         COUNT(DISTINCT docente) AS Total_docentes
                         FROM		fichas_docencia
                         GROUP BY	estabelecimento, grau
                         ORDER by   Total_docentes DESC
                         LIMIT 20"""
                cursor.execute(cmd)
                r = cursor.fetchall()
                for row in r:
                    file.write('<tr>\n')
                    file.write('<td>%s</td>' %(row[0].encode('utf-8'))) # Creates a new colum in the row)
                    file.write('<td>%s</td>' %(row[1].encode('utf-8'))) # Creates a new colum in the row)
                    file.write('<td>%d</td>' %(row[2])) # Creates a new colum in the row)
                    file.write('</tr>\n')
            except:
                print ('ERROR')
            pass
            file.write('</tr>\n')
        
        cursor.close()                
        file.write('</table>\n') # closes the table
        # some nedded links
        file.write('<table border =1 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n') # new table
        file.write('<td><a href="../index.html">-inicio-</a></td>')
        file.write('</table>\n') # closes the table
        
        file.write('</body>\n')
        file.write('</html>\n')
        file.close # close the file
    pass
    
    
    #-------------------------------------------------------------------------
    # List of institution per degree in the selected year
    #-------------------------------------------------------------------------    
    def list_inst_ano(self, ano):
        print('Processing list of institution per degree in the year 200{0}'.format(ano))
        path = 'HTML/list_est_200{0}.html'.format(ano)
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        list_inst_ano = open(path,"wb") # opens file for writing
        
        # Then start to build the html code
        list_inst_ano.write('<html>\n')
        list_inst_ano.write('<head>')
        list_inst_ano.write('<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />')
        list_inst_ano.write('<h1 align="center">Lista de estabelecimentos no ano de 200{0}</h1>\n'.format(ano)) # Header
        list_inst_ano.write('</head>')
        # some nedded links
        list_inst_ano.write('<body>\n')
        list_inst_ano.write('<table border =1 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n')
        list_inst_ano.write('<td><a href="../index.html">-inicio-</a></td>')
        if ano > 0:
            list_inst_ano.write('<td><a href="list_est_200{0}.html">-Ano Anterior-</a></td>'.format(ano - 1))
            pass
        if ano != 9:
            list_inst_ano.write('<td><a href="list_est_200{0}.html">-Ano Seguinte-</a></td>'.format(ano + 1))
            pass
        list_inst_ano.write('</table>\n') # closes the table
        list_inst_ano.write('<p></p>\n')
        
        # Here starts the table and we can change the appearence
        list_inst_ano.write('<table border =2 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC99>\n') # Creates a table
        
        conn = sqlite3.connect('rebides200{0}.db'.format(ano))
        cursor = conn.cursor()
        try:
            cmd = """SELECT designacao FROM estabelecimentos"""
            cursor.execute(cmd)
            r = cursor.fetchall()
            i = 1
            for row in r:
                # Now print fetched result
                est = row[0].encode('utf-8')
                #print "%s" %(est)
                est = row[0].encode('utf-8')
                list_inst_ano.write('<tr>\n') # Creates a new row
                list_inst_ano.write('<td> %d </td>' %(i)) # Creates a new colum in the r
                list_inst_ano.write('<td> %s </td>' %(est)) # Creates a new colum in the row
                list_inst_ano.write('</tr>\n')# Closes the row
                i += 1
        except:
            print ('ERROR')
        pass
        
        cursor.close()
        list_inst_ano.write('</table>\n') # closes the table
        # some nedded links
        list_inst_ano.write('<table border =1 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n')
        list_inst_ano.write('<td><a href="../index.html">-inicio-</a></td>')
        if ano > 0:
            list_inst_ano.write('<td><a href="list_est_200{0}.html">-Ano Anterior-</a></td>'.format(ano - 1))
            pass
        if ano != 9:
            list_inst_ano.write('<td><a href="list_est_200{0}.html">-Ano Seguinte-</a></td>'.format(ano + 1))
            pass
        list_inst_ano.write('</table>\n') # closes the table
        
        list_inst_ano.write('</body>\n')
        list_inst_ano.write('</html>\n')
        list_inst_ano.close # close the file
        
        
    #-------------------------------------------------------------------------
    # List of teachers per degree in the selected year
    #-------------------------------------------------------------------------
    def list_prof_grau_ano(self, ano):
        print('Processing list of teachers per degree in the year 200{0}'.format(ano))
        path = 'HTML/list_prof_grau_ano_200{0}.html'.format(ano)
        try:
            os.stat(os.path.dirname(path))
            pass
        except:
            os.mkdir(os.path.dirname(path))
            pass
        
        list_prof_grau_ano = open(path ,"wb") # opens file for writing
        # Then start to build the html code
        list_prof_grau_ano.write('<html>\n')
        list_prof_grau_ano.write('<head>')
        list_prof_grau_ano.write('<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />')
        list_prof_grau_ano.write('<h1 align="center">Lista de Professores por Grau no ano de 200{0}</h1>\n'.format(ano)) # Header
        list_prof_grau_ano.write('</head>')
        list_prof_grau_ano.write('<body>\n')
        # some nedded links
        list_prof_grau_ano.write('<table border =1 align="center" align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n')
        list_prof_grau_ano.write('<td><a href="../index.html">-inicio-</a></td>')
        if ano > 0:
            list_prof_grau_ano.write('<td><a href="list_prof_grau_ano_200{0}.html">-Ano Anterior-</a></td>'.format(ano - 1))
            pass
        if ano != 9:
            list_prof_grau_ano.write('<td><a href="list_prof_grau_ano_200{0}.html">-Ano Seguinte-</a></td>'.format(ano + 1))
            pass
        list_prof_grau_ano.write('</table>\n') # closes the table
        list_prof_grau_ano.write('<p></p>\n')
        
        # Here starts the table and we can change the appearence
        list_prof_grau_ano.write('<table border =2 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC99>\n') # Creates a table
        list_prof_grau_ano.write('<td bgcolor="#CCCC00"><strong>NOME DO PROFESSOR</strong></td><td bgcolor="#CCCC00"><strong>GRAU ACADÉMICO</strong></td>') # Creates a new colum in the row
        
        conn = sqlite3.connect('rebides200{0}.db'.format(ano))
        cursor = conn.cursor()
        try:
            cmd = """SELECT  ano, docente, grau
                     FROM fichas_docencia
                     INNER JOIN docentes
                     ON fichas_docencia.docente = docentes.nome_completo
                     INNER JOIN graus
                     ON fichas_docencia.grau = graus.designacao
                     ORDER BY grau, docente"""
            cursor.execute(cmd)
            r = cursor.fetchall()
            for row in r:
                # Now print fetched result
                #anos = row[0]
                docente = row[1].encode('utf-8')
                grau = row[2].encode('utf-8')
                list_prof_grau_ano.write('<tr>\n') # Creates a new row
                list_prof_grau_ano.write('<td> %s </td>' %(docente)) # Creates a new colum in the row
                list_prof_grau_ano.write('<td> %s </td>' %(grau)) # Creates a new colum in the row                 
                list_prof_grau_ano.write('</tr>\n')# Closes the row

        except:
            print ('ERROR')
        pass
        
        cursor.close()
        list_prof_grau_ano.write('</table>\n') # closes the table
        list_prof_grau_ano.write('<table border =1 align="center" bordercolor=000000 cellspacing=0 bgcolor=#CCCC900>\n')
        list_prof_grau_ano.write('<td><a href="../index.html">-inicio-</a></td>')
        if ano > 0:
            list_prof_grau_ano.write('<td><a href="list_prof_grau_ano_200{0}.html">-Ano Anterior-</a></td>'.format(ano - 1))
            pass
        if ano != 9:
            list_prof_grau_ano.write('<td><a href="list_prof_grau_ano_200{0}.html">-Ano Seguinte-</a></td>'.format(ano + 1))
            pass
        list_prof_grau_ano.write('</table>\n') # closes the table
        list_prof_grau_ano.write('<p></p>\n')
        list_prof_grau_ano.write('</body>\n')
        list_prof_grau_ano.write('</html>\n')
        list_prof_grau_ano.close # close the file

mhtml = MyHTML()
##mhtml.criar_index()
##mhtml.tot_prof_ano()
##mhtml.total_prof_est_ano()
##mhtml.total_prof_grau_ano()
##mhtml.total_prof_grau_est_ano()
##for i in range(0, 10):
##    mhtml.list_inst_ano(i)
##   mhtml.list_prof_grau_ano(i)

